package com.yw.atp.service;

import java.util.Date;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import com.yw.atp.model.Command;

@Repository
@Transactional
public class CommandService{
	@PersistenceContext
    private EntityManager em;
	public long getCommandsCount(Command command,Date b,Date e,String commType){
		StringBuffer sb = new StringBuffer();
		sb.append("select count(*) FROM  Command c where c.content like :content and c.sendType like :sendType and c.commType = :commType and c.status !='1' ");
		if(command.getIssuer()!=null&&command.getIssuer().getId()!=null&&command.getIssuer().getId().intValue()>0){
			sb.append(" and c.issuer.id = :issuerID ");
		}
		if(command.getSender()!=null&&command.getSender().getId()!=null&&command.getSender().getId().intValue()>0){
			sb.append(" and c.sender.id = :senderID ");
		}
		if(b!=null){
			sb.append(" and c.createdAt >= :beginDate ");
		}
		if(e!=null){
			sb.append(" and c.createdAt <= :endDate ");
		}
		sb.append(" order by c.status asc,c.id desc ");
		Query query = em.createQuery(sb.toString());
		String content   = getValue(command.getContent(),true);
		String sendType = getValue(command.getSendType(),false);
		query.setParameter("content",content);
		query.setParameter("sendType",sendType);
		query.setParameter("commType",commType);
		if(command.getIssuer()!=null&&command.getIssuer().getId()!=null&&command.getIssuer().getId().intValue()>0){
			query.setParameter("issuerID",command.getIssuer().getId());
		}
		if(command.getSender()!=null&&command.getSender().getId()!=null&&command.getSender().getId().intValue()>0){
			query.setParameter("senderID",command.getSender().getId());
		}
		if(b!=null){
			query.setParameter("beginDate",b);
		}
		if(e!=null){
			query.setParameter("endDate",e);
		}
		return (Long)query.getSingleResult();
	}
	
	public List<Command> getCommands(Command command,Date b,Date e,String commType,int begin ,int end){
		StringBuffer sb = new StringBuffer();
		sb.append("select c FROM  Command c where c.content like :content and c.sendType like :sendType and c.commType = :commType and c.status !='1' ");
		if(command.getIssuer()!=null&&command.getIssuer().getId()!=null&&command.getIssuer().getId().intValue()>0){
			sb.append(" and c.issuer.id = :issuerID ");
		}
		if(command.getSender()!=null&&command.getSender().getId()!=null&&command.getSender().getId().intValue()>0){
			sb.append(" and c.sender.id = :senderID ");
		}
		if(b!=null){
			sb.append(" and c.createdAt >= :beginDate ");
		}
		if(e!=null){
			sb.append(" and c.createdAt <= :endDate ");
		}
		sb.append(" order by c.status asc,c.id desc ");
		Query query = em.createQuery(sb.toString());
		String content   = getValue(command.getContent(),true);
		String sendType = getValue(command.getSendType(),false);
		query.setParameter("content",content);
		query.setParameter("sendType",sendType);
		query.setParameter("commType",commType);
		if(command.getIssuer()!=null&&command.getIssuer().getId()!=null&&command.getIssuer().getId().intValue()>0){
			query.setParameter("issuerID",command.getIssuer().getId());
		}
		if(command.getSender()!=null&&command.getSender().getId()!=null&&command.getSender().getId().intValue()>0){
			query.setParameter("senderID",command.getSender().getId());
		}
		if(b!=null){
			query.setParameter("beginDate",b);
		}
		if(e!=null){
			query.setParameter("endDate",e);
		}
		query.setFirstResult(begin);
		query.setMaxResults(end);
		return (List<Command>)query.getResultList();
	}
	
	public List<Command> getCommands(Command command,String mobileID){
		StringBuffer sb = new StringBuffer();
		sb.append("select c FROM  Command c ,Staff s ,Profession p where s.mobileID = :mobileID and c.commType = :commType and c.status !='1' and (s in elements(c.staffList) or ");
		sb.append("s.department in elements(c.deparList) or exists (select p from Profession p where p in  elements(s.professions) and p in elements(c.proList)))");
		if(command.getIssuer()!=null&&command.getIssuer().getId()!=null&&command.getIssuer().getId().intValue()>0){
			sb.append(" and c.issuer.id = :issuerID ");
		}
		if(command.getSender()!=null&&command.getSender().getId()!=null&&command.getSender().getId().intValue()>0){
			sb.append(" and c.sender.id = :senderID ");
		}
		sb.append(" order by c.status asc,c.id desc ");
		Query query = em.createQuery(sb.toString());
		String commType   = command.getCommType();
		query.setParameter("commType",commType);
		query.setParameter("mobileID",mobileID);
		if(command.getIssuer()!=null&&command.getIssuer().getId()!=null&&command.getIssuer().getId().intValue()>0){
			int issuerID  =  command.getIssuer().getId();
			query.setParameter("issuerID",command.getIssuer().getId());
		}
		if(command.getSender()!=null&&command.getSender().getId()!=null&&command.getSender().getId().intValue()>0){
			int senderID  =  command.getSender().getId();
			query.setParameter("senderID",senderID);
		}
		return (List<Command>)query.getResultList();
	}
	
	public String getValue(String str,boolean canNull){
		if(canNull){
			if(str==null||"".equals(str)){ return "%%"; }else{ return "%"+str+"%";}
		}else{
			if(str==null||"".equals(str)){ return "%%"; }else{ return str;}
		}
	}
	
	public void setTop(String commandIds,boolean top){
		Query query = em.createQuery("update Command c set c.top = :top where c.id in ("+commandIds+")");
		query.setParameter("top",top);
    	query.executeUpdate();
	}
	
	public List<Command> getPollingCommands(Integer maxID,String ids,String mobileID,String commType){
		StringBuffer sb = new StringBuffer();
		sb.append("select c FROM  Command c ,Staff s where s.mobileID = :mobileID and c.status !='1' and (c.commType = '1' or c.commType = '3') and c.id > :maxID and c.id not in ("+ids+") and (s in elements(c.staffList) or ");
		sb.append("s.department in elements(c.deparList) or exists (select p from Profession p where p in  elements(s.professions) and p in elements(c.proList)))");
		sb.append(" order by c.id asc ");
		Query query = em.createQuery(sb.toString());
		query.setParameter("maxID",maxID);
		//query.setParameter("commType",commType);
		query.setParameter("mobileID",mobileID);
		query.setFirstResult(0);
        query.setMaxResults(10);
		return (List<Command>)query.getResultList();
	}
	
	public List<Command> getPollingReadCommands(Integer maxID,String ids,String mobileID,String commType){
		StringBuffer sb = new StringBuffer();
		sb.append("select c FROM  Command c ,Staff s where s.mobileID = :mobileID and c.status !='1' and (c.commType = '1' or c.commType = '3') and c.id > :maxID and c.id not in ("+ids+") and (s in elements(c.staffList) or ");
		sb.append("s.department in elements(c.deparList) or exists (select p from Profession p where p in  elements(s.professions) and p in elements(c.proList))) and exists (select r from Reply r where r.owner = s and r.command = c)");
		sb.append(" order by c.status asc,c.id asc ");
		Query query = em.createQuery(sb.toString());
		query.setParameter("maxID",maxID);
		//query.setParameter("commType",commType);
		query.setParameter("mobileID",mobileID);
		query.setFirstResult(0);
        query.setMaxResults(30);
		return (List<Command>)query.getResultList();
	}
	
	public List<Command> getPollingNewCommands(Integer maxID,String ids,String mobileID,String commType){
		StringBuffer sb = new StringBuffer();
		sb.append("select c FROM  Command c ,Staff s where s.mobileID = :mobileID and c.status !='1' and (c.commType = '1' or c.commType = '3') and c.id > :maxID and c.id not in ("+ids+") and (s in elements(c.staffList) or ");
		sb.append("s.department in elements(c.deparList) or exists (select p from Profession p where p in  elements(s.professions) and p in elements(c.proList))) and not exists (select r from Reply r where r.owner = s and r.command = c)");
		sb.append(" order by c.status asc,c.id asc ");
		Query query = em.createQuery(sb.toString());
		query.setParameter("maxID",maxID);
		//query.setParameter("commType",commType);
		query.setParameter("mobileID",mobileID);
		query.setFirstResult(0);
        query.setMaxResults(30);
		return (List<Command>)query.getResultList();
	}
}
